<?php

/**
 * bom actions.
 *
 * @package    student_manager
 * @subpackage bom
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 2288 2006-10-02 15:22:13Z fabien $
 */
class bomActions extends autobomActions
{

public function executeChangeNumber(){
     $this->getResponse()->setTitle('WMS-Change-No-BOM');
    $id = $this->getRequestParameter('id');
    if ($this->getRequest()->isMethod('post')){    	
    	$wono = $this->getRequestParameter('wono');
    	if ($id && $wono){
    		$bom = BOMPeer::retrieveByPK($id);
    		if ($bom){
	    		$bom->setWono($wono);
	    		$bom->save();
	    		$this->getUser()->setFlash('notice',  'change BOM number successfully!');
	    		return $this->redirect("bom/list?id=$id");
    		}
    	}
    }
    
    $bom = BOMPeer::retrieveByPK($id);
    $this->wono = $bom->getWono();
    $this->id = $id;
}
//HAM CHANGE DATE
public function executeChangeDate()
{
    if (!$this->getUser()->hasCredential('EDIT_BOM'))
    {
        $this->getUser()->setFlash('notice',  'You dont have permission to edit BOM!');
        return $this->redirect("bom/list?id=$id");
    }     
    $id = $this->getRequestParameter('id');     
    $obj = BOMPeer::retrieveByPK($id);
    if (!$obj)
    {
        $this->getUser()->setFlash('notice',  'BOM is not exist!');
        return $this->redirect("bom/list");
    }
    if ($obj->getApprovalstatus() != CommonValues::$gMRApprovalStatus['key']['Approved'])
    {
        $this->getUser()->setFlash('notice',  'This BOM is not yet published!');
        return $this->redirect("bom/list?id=$id");
    }    
    $this->getResponse()->setTitle('WMS-BOM-Change-Date-'.$obj->getWono());
     //Lay ve Id cua Tally In  
    if ($this->getRequest()->isMethod('post'))
    { 
        //Lay ve ngay can thay doi
    	$date = $this->getRequestParameter('changedate');
    	if ($date)
        {           
            $obj->setCreatedAt($date);
            $obj->save();     
            $this->getUser()->setFlash('notice',  'Change date of '.$obj->getWono().' successfully!');
            return $this->redirect("bom/list?id=$id");
        }
    }
    $this->changedate = $obj->getCreatedAt();     
    $this->id = $id;
}	

public function executeViewNotes(){
     $this->getResponse()->setTitle('WMS-Viewnote-BOM');
    $this->bom = $this->getBOMOrCreate();       
    $this->labels = $this->getLabels();
}

public function executeView(){
      $this->getResponse()->setTitle('WMS-View-BOM');
    $this->bom = $this->getBOMOrCreate();       
    $this->labels = $this->getLabels();
}

public function executePrint(){
      $this->getResponse()->setTitle('WMS-Print-BOM');
    $id = $this->getRequestParameter('id');
    $this->bom = BOMPeer::retrieveByPK($id);
    if (!$this->bom) {
        $this->getRequest()->setError('print', 'This BOM is not available.');        
        $this->forward('bom', 'list');
    }
    
    
    $c = new Criteria();
    $c->add(ComponentsPeer::COMPONENTGROUP_ID , $this->bom->getComponentgroupId());
    $this->compsList = ComponentsPeer::doSelect($c);
    
    if (!$this->compsList){
        $this->getRequest()->setError('print', 'There is no component in this BOM.');        
        $this->forward('bom', 'list');
    }
    
    $c = new Criteria();
    $c->add(MRPeer::BOM_ID , $id);
    $this->mr = MRPeer::doSelectOne($c);

    $user = sfGuardUserPeer::retrieveByPK($this->bom->getUserId());
    $this->createName = $user->getFullName();    
}
public function executeRevise(){
      $this->getResponse()->setTitle('WMS-Revise-BOM');
    $Id = $this->getRequestParameter('id');
    $bom = BOMPeer::retrieveByPK($Id);
    if (!$bom){
        $this->getRequest()->setError('revise', 'This BOM is not available!');
        $this->forward('BOM', 'list');
    }
    if ($bom->getStatus() != CommonValues::$gBomStatus['key']['In progress'] &&
            $bom->getApprovalstatus() != CommonValues::$gMRApprovalStatus['key']['Approved']) {
        $this->getRequest()->setError('revise', 'Could not revise this BOM!');
        $this->forward('BOM', 'list');
    }
    $this->redirect('bom/create?prepare_revise_Id='.$Id);
}
 protected function addFiltersCriteria($c)
  {
  
    if (isset($this->filters['wono_is_empty']))
    {
      $criterion = $c->getNewCriterion(BOMPeer::WONO, '');
      $criterion->addOr($c->getNewCriterion(BOMPeer::WONO, null, Criteria::ISNULL));
      $c->add($criterion);
    }
    else if (isset($this->filters['wono']) && $this->filters['wono'] !== '')
    {
      $c->add(BOMPeer::WONO, '%'.strtr($this->filters['wono'], '*', '%').'%', Criteria::LIKE);
    }
    if (isset($this->filters['project_id_is_empty']))
    {
      $criterion = $c->getNewCriterion(BOMPeer::PROJECT_ID, '');
      $criterion->addOr($c->getNewCriterion(BOMPeer::PROJECT_ID, null, Criteria::ISNULL));
      $c->add($criterion);
    }
    else if (isset($this->filters['project_id']) && $this->filters['project_id'] !== '')
    {
      $c->add(BOMPeer::PROJECT_ID, $this->filters['project_id']);
    }
    if (isset($this->filters['client_id_is_empty']))
    {
      $criterion = $c->getNewCriterion(BOMPeer::CLIENT_ID, '');
      $criterion->addOr($c->getNewCriterion(BOMPeer::CLIENT_ID, null, Criteria::ISNULL));
      $c->add($criterion);
    }
    else if (isset($this->filters['client_id']) && $this->filters['client_id'] !== '')
    {
      $c->add(BOMPeer::CLIENT_ID, $this->filters['client_id']);
    }
    if (isset($this->filters['created_at_is_empty']))
    {
      $criterion = $c->getNewCriterion(BOMPeer::CREATED_AT, '');
      $criterion->addOr($c->getNewCriterion(BOMPeer::CREATED_AT, null, Criteria::ISNULL));
      $c->add($criterion);
    }
    else if (isset($this->filters['created_at']))
    {
      if (isset($this->filters['created_at']['from']) && $this->filters['created_at']['from'] !== '')
      {
        $criterion = $c->getNewCriterion(BOMPeer::CREATED_AT, $this->filters['created_at']['from'], Criteria::GREATER_EQUAL);
      }
      if (isset($this->filters['created_at']['to']) && $this->filters['created_at']['to'] !== '')
      {
        if (isset($criterion))
        {
          $criterion->addAnd($c->getNewCriterion(BOMPeer::CREATED_AT, $this->filters['created_at']['to'], Criteria::LESS_EQUAL));
        }
        else
        {
          $criterion = $c->getNewCriterion(BOMPeer::CREATED_AT, $this->filters['created_at']['to'], Criteria::LESS_EQUAL);
        }
      }

      if (isset($criterion))
      {
        $c->add($criterion);
      }
    }
    
    if (isset($this->filters['status_is_empty']))
    {
      $criterion = $c->getNewCriterion(BOMPeer::STATUS, '');
      $criterion->addOr($c->getNewCriterion(BOMPeer::STATUS, null, Criteria::ISNULL));
      $c->add($criterion);
    }
    else if (isset($this->filters['status']) && $this->filters['status'] !== '')
    {
      $c->add(BOMPeer::STATUS, $this->filters['status']);
    }
    else if (isset($this->filters['status']) && $this->filters['status'] == '')
    {
      //$c->add(BOMPeer::STATUS, CommonValues::$gBomStatus['key']['Revised'], Criteria::NOT_EQUAL);
    }
    if (isset($this->filters['approvalstatus_is_empty']))
    {
      $criterion = $c->getNewCriterion(BOMPeer::APPROVALSTATUS, '');
      $criterion->addOr($c->getNewCriterion(BOMPeer::APPROVALSTATUS, null, Criteria::ISNULL));
      $c->add($criterion);
    }
    else if (isset($this->filters['approvalstatus']) && $this->filters['approvalstatus'] !== '')
    {
      $c->add(BOMPeer::APPROVALSTATUS, $this->filters['approvalstatus']);
    }
  }

  public function executeReject()
  {
      $this->getResponse()->setTitle('WMS-Reject-BOM');
      $id = $this->getRequestParameter('id');
      $backURL = $this->getRequestParameter('backURL');
      $notes = $this->getRequestParameter('notes');

      $bom = BOMPeer::retrieveByPK($id);
      if (!$bom) {
            $this->getUser()->setFlash('notice', 'Could not reject this BOM because it is not available!');
            $this->redirect($backURL);
      }      
      //save BOM
      $bom->setStatus(CommonValues::$gBomStatus['key']['Rejected']);
      $bom->setApprovalstatus(CommonValues::$gMRApprovalStatus['key']['Rejected']);
      $bom->setNotes($notes);
      $bom->save();      
      
      $this->getUser()->setFlash('notice', 'This BOM was rejected successfully!');
      $this->redirect($backURL);
  }
  

  public function executeDelete()
  {
     $this->getResponse()->setTitle('WMS-Del-BOM');
    $this->bom = BOMPeer::retrieveByPk($this->getRequestParameter('id'));
    $this->forward404Unless($this->bom);
    
    if ($this->bom->getStatus() != CommonValues::$gBomStatus['key']['Initial']
            && !$this->getUser()->hasGroup('Administrator') ){
        $this->getRequest()->setError('delete', 'Could not delete the selected BOM. It was published.');
        return $this->forward('bom', 'list');
    }

    try
    {
      $c = new Criteria();
      $c->add(MRPeer::BOM_ID, $this->bom->getId());
      $MRList = MRPeer::doSelect($c);
      if ($MRList){
          foreach ($MRList as $MR)
          {
            $c = new Criteria();
            $c->add(POPeer::MRNO, $MR->getId());
            $c->add(POPeer::COMPCATEGORY, 0);
            
            $POList = POPeer::doSelect($c);

            if ($POList){
                foreach ($POList as $PO)
                {
                    $c1 = new Criteria();
                    $c1->add(TallyInPeer::TALLYNO, $PO->getId());
                    $c1->add(TallyInPeer::TYPE, CommonValues::$gTallyInType['key']['Supplier']);
                    $TIList = TallyInPeer::doSelect($c1);
                    if ($TIList)
                    {
                        foreach ($TIList as $TI)
                        {
                            $TI->delete();
                        }
                    }
                    $PO->delete();
                }
            }
            $MR->delete();
          }
      }
      $this->deleteBOM($this->bom);
    }
    catch (PropelException $e)
    {
      $this->getRequest()->setError('delete', 'Could not delete the selected Bom. Make sure it does not have any associated items.');
      return $this->forward('bom', 'list');
    }

    return $this->redirect('bom/list');
  }

     public function executeList()
              {
                  $this->getResponse()->setTitle('WMS-BOM-List');
                $this->processSort();
            
                $this->processFilters();
            
                $this->filters = $this->getUser()->getAttributeHolder()->getAll('sf_admin/bom/filters');
            
                // pager
                $this->pager = new sfPropelPager('BOM', 25);
                $c = new Criteria();
                $c->addDescendingOrderByColumn(BOMPeer::WONO);
                $this->addSortCriteria($c);
                $this->addFiltersCriteria($c);
                
                $this->pager->setCriteria($c);
                $this->pager->setPage($this->getRequestParameter('page', $this->getUser()->getAttribute('page', 1, 'sf_admin/bom')));
                $this->pager->init();
                // save page
                if ($this->getRequestParameter('page')) {
                    $this->getUser()->setAttribute('page', $this->getRequestParameter('page'), 'sf_admin/bom');
                }
              }
  
  public function executeCancel()
  {
      $id = $this->getRequestParameter('id');
      $bom = BOMPeer::retrieveByPK($id);
      if (!$bom || $bom->getStatus() != CommonValues::$gBomStatus['key']['In progress']
      || $bom->getApprovalstatus() != CommonValues::$gMRApprovalStatus['key']['Waiting for approval']) {
            $this->getRequest()->setError('edit', 'Could not cancel this BOM because it is not available!');
            $this->forward('BOM', 'list');
      }      
      
      $bom->setStatus(CommonValues::$gBomStatus['key']['Cancel']);
      $bom->setApprovalstatus(CommonValues::$gMRApprovalStatus['key']['Cancel']);
      $bom->save();
      
      
      $this->getUser()->setFlash('notice', 'This BOM was cancel successfully!');
      $this->forward('BOM', 'list');
  }
	  
	public function executeEdit()
	  {
            $this->getResponse()->setTitle('WMS-Edit-BOM');
	    $this->bom = $this->getBOMOrCreate();
	    
	    //revise BOM
	    $reviseId = $this->getRequestParameter('prepare_revise_Id');
	    if ($reviseId != '' && $reviseId != null) {
	       $reviseBom = BOMPeer::retrieveByPK($reviseId);
	       if (!$reviseBom) {
                        $this->getRequest()->setError('edit', 'Could not revise this BOM because it is not available!');
                        $this->forward('BOM', 'list');
	       }
	       $this->bom = $reviseBom->copy();
	       $this->bom->setReviseId($reviseId);
	       $this->bom->setStatus(CommonValues::$gBomStatus['key']['Initial']);
               $this->bom->setApprovalstatus(CommonValues::$gMRApprovalStatus['key']['Initial']);
	       $componentGroup = new ComponentGroup();
	       $componentGroup->setType(1);
	       $componentGroup->save();
	       $componentGroupId = $reviseBom->getComponentgroupId();
	       $c= new Criteria();
	       $c->add(ComponentsPeer::COMPONENTGROUP_ID , $componentGroupId);
	       $compsList = ComponentsPeer::doSelect($c);
	       foreach ($compsList  as $comps) {
	           $compsNew = $comps->copy();
	           $compsNew->setComponentId($comps->getComponentId());
	           $compsNew->setComponentgroupId($componentGroup->getId());	           
	           $compsNew->setQty1($comps->getQty1());
	           $compsNew->setQty2($comps->getQty2());	       	
	           $compsNew->setQty4($comps->getQty4());	       	
	           $compsNew->save();
	       }  
	       $this->bom->setComponentgroupId($componentGroup->getId());
	       $this->bom->save();
	    }
                
	    //check status for edit BOM
            if ($this->bom->getStatus() != CommonValues::$gBomStatus['key']['Initial']
            && $this->bom->getStatus() != CommonValues::$gBomStatus['key']['Cancel']
            && $this->bom->getStatus() != CommonValues::$gBomStatus['key']['Rejected']
            ) {
                $this->getRequest()->setError('edit', 'Cannot edit this BOM. It was published.');
                $this->forward('bom', 'list');
            }            
	
	    if ($this->getRequest()->isMethod('post'))
	    {
	      $this->updateBOMFromRequest();
              $this->bom->setStatus(CommonValues::$gBomStatus['key']['Initial']);
              $this->bom->setApprovalstatus(CommonValues::$gMRApprovalStatus['key']['Initial']);
	      $this->bom->setWono(CommonValues::GenerateAutoNumber('BOM', $this->bom->getWono(), 'BOM')) ;
	      
	      //Tien code here
	      $qtys = $this->getRequestParameter('qtys');
	      $rowIds = $this->getRequestParameter('rowIds');
	      $expdates = $this->getRequestParameter('expdates');
	      $suppliedByClients = $this->getRequestParameter('suppliedByClients');
        
	      if ($this->bom->getProjectId()) {
	      	$project = ProjectPeer::retrieveByPK($this->bom->getProjectId());	      	
	      	if ($project && $project->getClientId()) $this->bom->setClientId($project->getClientId());
	      }
	      
	      if (isset($rowIds) && count($rowIds) && isset($qtys) && count($qtys) ) {
	      	
	      	//remove old component group id
	      	if ($this->bom->getComponentgroupId()) {
	      	    $c = new Criteria();
	      	    $c->add(ComponentsPeer::COMPONENTGROUP_ID , $this->bom->getComponentgroupId());
	      	    ComponentsPeer::doDelete($c);	      	    
	      	}
	      	
	      	$componentGroup = new ComponentGroup();
	      	if ($componentGroup)    {
	      		$componentGroup->setType(1);
	      		$componentGroup->save();
	      		$componentGroupId = $componentGroup->getId();
	      		
	      		if ($componentGroupId) {
	      			$this->bom->setComponentgroupId($componentGroupId);
	      			for($i=0; $i < count($rowIds); $i++){
                                    if ($qtys[$i] > 0){
	      				$components = new Components();
	      				if ($components){
	      					$components->setComponentgroupId($componentGroupId);
	      					$components->setComponentId($rowIds[$i]);
	      					$components->setQty1($qtys[$i]);
	      					$components->setQty4($qtys[$i]);
	      					$components->setQty2(0);
                                                if ($expdates[$i]){
                                                    $components->setDate(CommonValues::ConvertDateToEN($expdates[$i]));
                                                }
	      					for ($j = 0; $j < count($suppliedByClients); $j++){
		      					if ($rowIds[$i] == $suppliedByClients[$j]){
		      						$components->setQty2(1);
		      						break;
		      					}
	      					}
	      					$components->save();
	      				}
                                    }
	      			}	      			
	      		}
	      	}
	      }
	      
	      $this->bom->setUserId($this->getUser()->getGuardUser()->getId());
	      try
	      {
	      	//add for save as approved
		      if ($this->getRequestParameter('save_as_approved'))
		      {		        
				$this->bom->setStatus(CommonValues::$gBomStatus['key']['In progress']);
				$this->bom->setApprovalstatus(CommonValues::$gMRApprovalStatus['key']['Approved']);

                                $approval = new Approval();
                                $approval->setType( CommonValues::$gApprovalType['key']['BOM']);
                                $approval->setObjectId($this->bom->getId());
                                $approval->setUserId(17);//support head
                                $approval->save();

                                $approval = new Approval();
                                $approval->setType( CommonValues::$gApprovalType['key']['BOM']);
                                $approval->setObjectId($this->bom->getId());
                                $approval->setUserId(16);//general director
                                $approval->save();
		      }
		      //end add
	        $this->saveBOM($this->bom);
	      }
	      catch (PropelException $e)
	      {
	        $this->getRequest()->setError('edit', 'Could not save the edited Boms.');
	        return $this->forward('bom', 'list');
	      }	      
	      
	      
	      
	      if ($this->bom->getReviseId()){
	           $this->getUser()->setFlash('notice',  'BOM '.$this->bom->getWono().' was revised successfully!');
	           $this->redirect('bom/list?id='.$this->bom->getId());
	      }
	
	      $this->getUser()->setFlash('notice', 'Your modifications have been saved');
	
	      if ($this->getRequestParameter('save_as_approved')){
	      	return $this->redirect('bom/list');
	      }
	      else if ($this->getRequestParameter('save_and_add'))
	      {
	        return $this->redirect('bom/create');
	      }
	      else if ($this->getRequestParameter('save_and_list'))
	      {
	        return $this->redirect('bom/list');
	      }
	      else
	      {
	        return $this->redirect('bom/edit?id='.$this->bom->getId());
	      }
	    }
	    else
	    {
	      $this->labels = $this->getLabels();
	    }
	  }
//publish a BOM
public function executePublish() {

        $id = $this->getRequestParameter('id');        
        $bom = BOMPeer::retrieveByPK($id);
    
        if (!$bom){
            $this->getRequest()->setError('publish', 'This BOM is not available.');        
            $this->forward('bom', 'list');
        }   
        
         if ($bom->getStatus() != CommonValues::$gBomStatus['key']['Initial']) {
            $this->getRequest()->setError('publish', 'This BOM was published adready.');        
            $this->forward('bom', 'list');
        }

        //update status for BOM
        $bom->setStatus(CommonValues::$gBomStatus['key']['In progress']);
        $bom->setApprovalstatus(CommonValues::$gMRApprovalStatus['key']['Waiting for approval']);
        $bom->setPublishtime(time());

        $bom->save();

        //if this user have permission with PR_APPROVE_MRMAIN
        //then this MR main was consider approved one times
        if ($this->getUser()->hasCredential('APPROVE_BOM')){
            $approval = new Approval();
            $approval->setType( CommonValues::$gApprovalType['key']['BOM']);
            $approval->setObjectId($bom->getId());
            $approval->setUserId($this->getUser()->getGuardUser()->getId());
            $approval->save();
        }

       return $this->redirect('bom/list?id='.$id);

       
       
        
}
	  
  public function handleErrorEdit()
  {
    $this->preExecute();
    $this->bom = $this->getBOMOrCreate();
    $this->updateBOMFromRequest();

    $this->labels = $this->getLabels();
    
    //transfer data to edit_form template
    $this->bom->qtys = $this->getRequestParameter('qtys');
    $this->bom->rowIds = $this->getRequestParameter('rowIds');
    $this->bom->expdates = $this->getRequestParameter('expdates');
    $this->bom->suppliedByClients = $this->getRequestParameter('suppliedByClients');
    
    return sfView::SUCCESS;
  }
	  
protected function getLabels()
  {
    return array(
      'bom{id}' => 'Id:',
      'bom{project_id}' => 'Project:',
      'bom{client_id}' => 'Client:',
      'bom{wono}' => 'BOM Number:',
      'bom{componentgroup_id}' => 'Comp List:',
      'bom{status}' => 'Status:',
      'bom{created_at}' => 'BOM Date:',
      'bom{notes}' => 'Notes:',
        'bom{created by}' => 'Created by:',
      'qtys'=>'Component List:',
        'expdates'=>'Date List:',
    );
  }
  
    
public function executeImportExcelToProject()
  {
        
    $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	$data->read('D:\PROJECT\SUNLIGHT\datamigration1of2\Project SG Pearl\Project SG Pearl.xls');
	
	
	if(isset($data->sheets[0]['cells'][3][8]) && trim($data->sheets[0]['cells'][3][8])){
		$projectname= trim($data->sheets[0]['cells'][3][8]);
		$c = new Criteria();
		$c->add(ProjectPeer::NAME, $projectname);
		$project = ProjectPeer::doSelectOne($c);
		if (!$project){
			$project = new Project();
			
			$project->setName($projectname);
			$client = new Client();
			$client->setName($projectname);
			$client->save();
			$project->setClientId($client->getId());
		
	  		if(isset($data->sheets[0]['cells'][4][8]) && trim($data->sheets[0]['cells'][4][8])){
				$project->setSaleno(trim($data->sheets[0]['cells'][4][8]));
	  		}
			if(isset($data->sheets[0]['cells'][5][8]) && trim($data->sheets[0]['cells'][5][8])){
				$project->setNotes(trim($data->sheets[0]['cells'][5][8]));
	  		}
	  		
	  		$project->save();
		}
  		
	}
	
	
	$bom = new BOM();
	$bom->setProjectId($project->getId());
	$bom->setClientId($project->getClientId());
	$bom->setWono(CommonValues::GenerateAutoNumber('BOM', null, 'BOM'));
	$bom->setStatus(CommonValues::$gBomStatus['key']['In progress']);
	$bom->setApprovalstatus(CommonValues::$gMRApprovalStatus['key']['Approved']);
	$compgroup = new ComponentGroup();
	$compgroup->setType(1);
	$compgroup->save();
	$bom->setComponentgroupId($compgroup->getId());
	$bom->save();
	
	for ($i = 10; $i <= 15; $i++){
		
		if(isset($data->sheets[0]['cells'][$i][4]) && trim($data->sheets[0]['cells'][$i][4])){
			$compsNew = new Components();
			$compsNew->setComponentgroupId($compgroup->getId());
			$productcode = trim($data->sheets[0]['cells'][$i][4]);
			$c = new Criteria();
			$c->add(ComponentPeer::PRODUCTCODE, $productcode);
			$comp = ComponentPeer::doSelectOne($c);
			if (!$comp){
				$comp = new Component();
				$comp->setProductcode($productcode);
				$comp->setDescription(trim($data->sheets[0]['cells'][$i][2]));
				$comp->setBrand(trim($data->sheets[0]['cells'][$i][5]));
				$comp->save();
			}
			$compsNew->setComponentId($comp->getId());
  		
	  		$compsNew->setQty1(trim($data->sheets[0]['cells'][$i][7]));
	  		$compsNew->setQty4($compsNew->getQty1());
	  		$compsNew->save();
		}
	}
	echo 'Successful!'; exit();
  }  
  
  
public function executeImportExcel()
  {
        
    $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\datamigration1of2\Project Sacombank\Project Sacombank.xls');
	
	$arr= array(
	   'MR'=> array(
		   '0'=> array(
				'BOMID'=>17,
				'SHEET'=>1,
				'STARTROW'=>13,
				'ENDROW'=>17,
				'ID'=>0,
				),
			),		
	   'PO'=> array(
		   '0'=> array(
				'MRID'=>'0',
				'SHEET'=>2,
				'STARTROW'=>17,			
				'ENDROW'=>18,
				'SUPPLIERROW'=>7,
				'ID'=>0,
				),
		   '1'=> array(
				'MRID'=>'0',
				'SHEET'=>3,
				'STARTROW'=>19,			
				'ENDROW'=>21,
				'SUPPLIERROW'=>8,
				'ID'=>0,
				),
			),		
	   'TLI'=> array(
		   '0'=> array(
				'POID'=>'0',
				'SHEET'=>6,
				'STARTROW'=>2,
				'ENDROW'=>26,
				'ID'=>0,
				),
			),		
	   'TLO'=> array(
		   '0'=> array(
				'POID'=>'0',
				'SHEET'=>2,
				'STARTROW'=>2,
				'ENDROW'=>10,
				'ID'=>0,
				),
			),			
	);
	
	foreach ($arr['MR'] as $key=>$mrData){
		$bom = BOMPeer::retrieveByPK($mrData['BOMID']);
		
		$MR = new MR();
		//set properties for new MR
	    $MR->setMrno(CommonValues::GenerateAutoNumber('MR', null, 'MR'));
	    $MR->setBomId($bom->getId());
	    $MR->setProjectId($bom->getProjectId());                              
	            
	    //set saleno
	    $project = ProjectPeer::retrieveByPK($bom->getProjectId());
	    $MR->setSaleno($project->getSaleno());
	            
	    $MR->setCategory(CommonValues::$gMRCategory['key']['Project']);
	    $MR->setStatus(CommonValues::$gMRStatus['key']['In progress']);
	    $MR->setApprovalstatus(CommonValues::$gMRApprovalStatus['key']['Approved']);
	    $MR->setPublishtime(time());
	    
	    $compgroup = new ComponentGroup();
	    $compgroup->setType(1);
	    $compgroup->save();
	    $MR->setComponentgroup($compgroup);
	    $MR->save();
	    $arr['MR'][$key]['ID']=$MR->getId();
	    
	    for ($i = $mrData['STARTROW']; $i <= $mrData['ENDROW']; $i++){
	    	
	    	$compsNew = new Components();
			$compsNew->setComponentgroupId($compgroup->getId());
				
	    	$productcode = trim($data->sheets[$mrData['SHEET']]['cells'][$i][4]);
	    	$description = trim($data->sheets[$mrData['SHEET']]['cells'][$i][2]);
	    	$qty = trim($data->sheets[$mrData['SHEET']]['cells'][$i][10]);
	    	$brand = trim($data->sheets[$mrData['SHEET']]['cells'][$i][5]);
	    	
	    	
			$c = new Criteria();
			$c->add(ComponentPeer::PRODUCTCODE, $productcode);
			$comp = ComponentPeer::doSelectOne($c);
			if (!$comp){			
				$comp = new Component();
				$comp->setProductcode($productcode);
				$comp->setDescription($description);
				$comp->setBrand($brand);
				$comp->save();
			}
			$compsNew->setComponentId($comp->getId());
	  		
	  		$compsNew->setQty1($qty);
	  		$compsNew->setQty4($qty);
	  		$compsNew->save();
	    }
	}
    
	foreach ($arr['PO'] as $key=>$poData){
		$supplierName = trim($data->sheets[$poData['SHEET']]['cells'][$poData['SUPPLIERROW']][1]);
		$c = new Criteria();
		$c->add(SupplierPeer::NAME, $supplierName);
		$sup = SupplierPeer::doSelectOne($c);
		if (!$sup){
			$sup = new Supplier();
			$sup->setName($supplierName);
			$sup->save();
		}
	    $PO = new PO();
		//set properties for new MR
	    $PO->setPono(CommonValues::GenerateAutoNumber('PO', null, 'PO'));
	    $PO->setCategory(CommonValues::$gMRCategory['key']['Project']);
	    $PO->setShippedvia(0);
	    $PO->setType(0);
	    $PO->setMrno($arr['MR'][$poData['MRID']]['ID']);
	    $PO->setSupplierId($sup->getId());
	    
	    $compgroup = new ComponentGroup();
	    $compgroup->setType(1);
	    $compgroup->save();
	    $PO->setComponentgroup($compgroup);
	    
	    	    
	    for ($i = $poData['STARTROW']; $i <= $poData['ENDROW']; $i++){
	    	
	    	$compsNew = new Components();
			$compsNew->setComponentgroupId($compgroup->getId());
				
	    	$productcode = trim($data->sheets[$poData['SHEET']]['cells'][$i][4]);
	    	$description = trim($data->sheets[$poData['SHEET']]['cells'][$i][2]);    	
	    	$brand = trim($data->sheets[$poData['SHEET']]['cells'][$i][3]);
	    	
	    	$qty = trim($data->sheets[$poData['SHEET']]['cells'][$i][5]);
	    	$unitprice = trim($data->sheets[$poData['SHEET']]['cells'][$i][7]);
	    	$totalprice = trim($data->sheets[$poData['SHEET']]['cells'][$i][8]);
	    	
	    	
			$c = new Criteria();
			$c->add(ComponentPeer::PRODUCTCODE, $productcode);
			$comp = ComponentPeer::doSelectOne($c);
			if (!$comp){			
				$comp = new Component();
				$comp->setProductcode($productcode);
				$comp->setDescription($description);
				$comp->setBrand($brand);
				$comp->save();
			}
			$compsNew->setComponentId($comp->getId());
	  		
	  		
			$compsNew->setQty1($qty);
			$compsNew->setQty2($unitprice);
			$compsNew->setQty3($totalprice);
	  		$compsNew->setQty4($qty);
	  		$compsNew->save();
	    }
	    $PO->setSubtotalprice(trim($data->sheets[$poData['SHEET']]['cells'][$poData['ENDROW'] + 1][8]));
	    $PO->setVat(trim($data->sheets[$poData['SHEET']]['cells'][$poData['ENDROW'] + 2][8]));
	    $PO->setShipandhand(trim($data->sheets[$poData['SHEET']]['cells'][$poData['ENDROW'] + 3][8]));
	    $PO->setTotalprice(trim($data->sheets[$poData['SHEET']]['cells'][$poData['ENDROW'] + 4][8]));
	    
	    $PO->setApprovalstatus(CommonValues::$gMRApprovalStatus['key']['Approved']);
	    $PO->save();
	    $arr['PO'][$key]['ID']=$PO->getId();
	}     
//    
//    foreach ($arr['TLI'] as $tliData){
//    
//	    $tallyin = new TallyIn();
//		//set properties for new MR
//	    $tallyin->setTallyincode(CommonValues::GenerateAutoNumber('TALLYIN', null, 'TI', 'm'));
//	    $tallyin->setType(0);
//	    $tallyin->setTallyno($arr['PO'][$tliData['POID']]['ID']);
//	    
//	    $compgroup = new ComponentGroup();
//	    $compgroup->setType(1);
//	    $compgroup->save();
//	    $tallyin->setComponentgroup($compgroup);
//	    
//	    for ($i = $tliData['STARTROW']; $i <= $tliData['ENDROW']; $i++){
//	    	
//	    	$compsNew = new Components();
//			$compsNew->setComponentgroupId($compgroup->getId());
//				
//	    	$productcode = trim($data->sheets[$tliData['SHEET']]['cells'][$i][3]);
//	    	$description = trim($data->sheets[$tliData['SHEET']]['cells'][$i][4]);    	
//	    	$unit = trim($data->sheets[$tliData['SHEET']]['cells'][$i][5]);
//	    	$brand = trim($data->sheets[$tliData['SHEET']]['cells'][$i][6]);
//	    	
//	    	$qty = trim($data->sheets[$tliData['SHEET']]['cells'][$i][7]);
//	    	
//	    	
//			$c = new Criteria();
//			$c->add(ComponentPeer::PRODUCTCODE, $productcode);
//			$comp = ComponentPeer::doSelectOne($c);
//			if (!$comp){			
//				$comp = new Component();
//				$comp->setProductcode($productcode);
//				$comp->setDescription($description);				
//				$comp->setBrand($brand);
//				$comp->save();
//			}
//			$compsNew->setComponentId($comp->getId());
//	  		
//	  		
//			$compsNew->setQty1($qty);
//	  		$compsNew->setQty4($qty);
//	  		$compsNew->save();
//	    }
//	    $tallyin->setIspublish(1);
//	    $tallyin->save();
//    }
    
//  foreach ($arr['TLO'] as $tloData){
//    
//	    $tallyout = new TallyOut();
//		//set properties for new MR
//	    $tallyout->setTallyoutcode(CommonValues::GenerateAutoNumber('TALLYOUT', null, 'TO', 'm'));
//	    $tallyout->setType(2);
//	    $tallyout->setTallyno($arr['MR']['0']['BOMID']);
//	    
//	    $compgroup = new ComponentGroup();
//	    $compgroup->setType(1);
//	    $compgroup->save();
//	    $tallyout->setComponentgroup($compgroup);
//	    
//	    for ($i = $tloData['STARTROW']; $i <= $tloData['ENDROW']; $i++){
//	    	
//	    	$compsNew = new Components();
//			$compsNew->setComponentgroupId($compgroup->getId());
//				
//	    	$productcode = trim($data->sheets[$tloData['SHEET']]['cells'][$i][3]);
//	    	$description = trim($data->sheets[$tloData['SHEET']]['cells'][$i][4]);    	
//	    	$unit = trim($data->sheets[$tloData['SHEET']]['cells'][$i][5]);
//	    	$brand = trim($data->sheets[$tloData['SHEET']]['cells'][$i][6]);
//	    	
//	    	$qty = trim($data->sheets[$tloData['SHEET']]['cells'][$i][7]);
//	    	
//	    	
//			$c = new Criteria();
//			$c->add(ComponentPeer::PRODUCTCODE, $productcode);
//			$comp = ComponentPeer::doSelectOne($c);
//			if (!$comp){			
//				$comp = new Component();
//				$comp->setProductcode($productcode);
//				$comp->setDescription($description);				
//				$comp->setBrand($brand);
//				$comp->save();
//			}
//			$compsNew->setComponentId($comp->getId());
//	  		
//	  		
//			$compsNew->setQty1($qty);
//	  		$compsNew->setQty4($qty);
//	  		$compsNew->save();
//	    }
//	    $tallyout->setIspublish(1);
//	    $tallyout->save();
//    }
    
	echo 'Successful!'; exit();
  }  
  public function executeRemovesame(){
  	$c = new Criteria();
  	$c->addAscendingOrderByColumn(ComponentTypePeer::ID);
  	$lst = ComponentTypePeer::doSelect($c);
  	$dau =array(count($lst));
  	for ($i = 0; $i < count($lst); $i++){
  		$dau[$i] = 0;
  	}
  	for ($i = 0; $i < count($lst); $i++){
  		if ($dau[$i] ==0)
	  		for ($j = $i+1; $j < count($lst); $j++){
	  			if ($lst[$i]->getName() == $lst[$j]->getName()){
	  				$dau[$j]=1;
	  			}
	  		}
  	}
  	
  	for ($i = 0; $i < count($lst); $i++){
  		if ($dau[$i] == 1){
  			
  			$lst[$i]->delete();
  		}
  	}
  	
  		
  }
public function executeListComp(){
  	$c = new Criteria();
  	$c->addAscendingOrderByColumn(ComponentPeer::ID);
  	$lst = ComponentPeer::doSelect($c);
  	
  	$arrHeader = array("No", "Product code", "Description");
  	$arrContent = array();
	 
	 
  	$dem =0;
  	for ($i = 0; $i < count($lst); $i++){
  		$c = new Criteria();
  		$c->add(WarehousePeer::COMPONENT_ID, $lst[$i]->getId());
  		$wh = WarehousePeer::doSelectOne($c);
  		if (!$wh){
  			echo $lst[$i]->getId().' ,'. $lst[$i]->getProductcode().' ,'.$lst[$i]->getDescription().'<br/>';
  			$dem++;
  			$arrContent[$dem][0]= $dem;
  			$arrContent[$dem][1]= $lst[$i]->getProductcode();
  			$arrContent[$dem][2]= $lst[$i]->getDescription();
  			
  		}
  	}
//  	$excel = new ExportExcel("NotSameProductcode.xls");
//	 $excel->setHeadersAndValues($arrHeader, $arrContent);
//	 $excel->GenerateExcelFile();
  	echo $dem;
  	exit();
  		
  }



public function executeChangeComp(){
  	  	
  	
	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\NotSameProductcode_adjusted.xls');
	
  	for ($i =2; $i <= 328; $i++){
  		$productcode = trim($data->sheets[0]['cells'][$i][2]);
  		$description = trim($data->sheets[0]['cells'][$i][3]);
  		$productcodeNew = trim($data->sheets[0]['cells'][$i][4]);
  		$type = trim($data->sheets[0]['cells'][$i][5]);  		
  		
  		if ($type == 'DEL'){
  			
  			continue;
  		}
  		
  		$c = new Criteria();
  		$c->add(ComponentPeer::PRODUCTCODE, $productcode);
  		$lstcomp = ComponentPeer::doSelect($c);
  		echo "$i: <br/>";
  		
  		if ($type =='EDIT'){
  			
  			$c = new Criteria();
  			$c->add(ComponentPeer::PRODUCTCODE, $productcodeNew);
  			$lstcompedit = ComponentPeer::doSelect($c);
  			
  			
  			if (!$lstcompedit){
  				echo "error 1: $productcodeNew <br/>"; continue;
  			}
  			
  			if (count($lstcompedit) > 1) {
  				echo "duplicated: $productcode new:  $productcodeNew  ";
  				echo "error 1"; exit();
  			}
  			$compedit = $lstcompedit[0];
  			
  			$c = new Criteria();
  			$c->add(WarehousePeer::COMPONENT_ID, $compedit->getId());
  			$wh = WarehousePeer::doSelectOne($c);
  			if (!$wh) {
  				echo "error 2"; exit();
  			}
  			if ($lstcomp) {
  				foreach ($lstcomp as $comp){
  					$c = new Criteria();
  					$c->add(ComponentsPeer::COMPONENT_ID, $comp->getId());
  					$lst = ComponentsPeer::doSelect($c);
  					if ($lst){
  						foreach ($lst as $obj){
  							$obj->setComponentId($compedit->getId());
  							$obj->save();
  						}
  					}
  					$comp->delete();
  				}
  			}
  		}elseif ($type=='create new') {
  			
  			if ($lstcomp) {
  				foreach ($lstcomp as $comp){
  					$c = new Criteria();
		  			$c->add(WarehousePeer::COMPONENT_ID, $comp->getId());
		  			$wh = WarehousePeer::doSelectOne($c);
		  			if (!$wh){
		  				$wh = new Warehouse();
		  				$wh->setComponentId($comp->getId());
		  				$wh->save();
		  			} else {
		  				echo "exist ". $comp->getProductcode(). "<br/>";		  				
		  			}
  				}
  			}
  		}
  	}
  	echo "success";
  	exit();
  		
  }
  

public function executeConvertCode(){
  	  	
  	
	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\Inventory1109.xls');
	
  	for ($i =5; $i <= 3109; $i++){
  		if (isset($data->sheets[2]['cells'][$i][3]) && isset($data->sheets[2]['cells'][$i][2]) && trim($data->sheets[2]['cells'][$i][3]) &&trim($data->sheets[2]['cells'][$i][2])){
  			$sevcode = trim($data->sheets[2]['cells'][$i][3]);
  			$clientcode = trim($data->sheets[2]['cells'][$i][2]);
  			if ($sevcode == $clientcode) continue; 
  			$c = new Criteria();
  			$c->add(ComponentPeer::PRODUCTCODE, $sevcode);
  			$lstcomp = ComponentPeer::doSelect($c);
  			if (!$lstcomp) {
  				$c1 = new Criteria();
	  			$c1->add(ComponentPeer::PRODUCTCODE, $clientcode);
	  			$lstcomp1 = ComponentPeer::doSelect($c1);
	  			if (!$lstcomp1) {
  					echo "error 1: $sevcode row: $i"; exit();
	  			} else {
	  				echo "replace: $sevcode $clientcode row: $i<br/>";
	  				continue;
	  			}
  			}
  			if (count($lstcomp) >1){
  				echo "error 2: $sevcode row: $i"; exit();
  			}
  			$comp = $lstcomp[0];
  			
  			$comp->setProductcode($clientcode);
  			$comp->save();
  		}
  	}
  	echo "success";
  	exit();
  		
  }
  public function executeAddApproval(){
  	for ($i = 1; $i <= 19; $i++){
  		$approval = new Approval();
            $approval->setType( CommonValues::$gApprovalType['key']['MR']);
            $approval->setObjectId($i);
            $approval->setUserId(17);//support head
            $approval->save();
            
            $approval = new Approval();
            $approval->setType( CommonValues::$gApprovalType['key']['MR']);
            $approval->setObjectId($i);
            $approval->setUserId(16);//general director
            $approval->save();
  	}
  	
  for ($i = 1; $i <= 24; $i++){
  		$approval = new Approval();
            $approval->setType( CommonValues::$gApprovalType['key']['PO']);
            $approval->setObjectId($i);
            $approval->setUserId(17);//support head
            $approval->save();
            
            $approval = new Approval();
            $approval->setType( CommonValues::$gApprovalType['key']['PO']);
            $approval->setObjectId($i);
            $approval->setUserId(16);//general director
            $approval->save();
  	}
  	echo "successful";
  	exit();
  }

  
public function executeChangeInventoryFile(){
  	  	
  	
	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\CODE - inventory 20-3.xls');
	
	$dem = 0;
	
  	for ($i =3; $i <= 2637; $i++){
  		if (isset($data->sheets[1]['cells'][$i][2]) ){
  			$sevcode = trim($data->sheets[1]['cells'][$i][2]);
  			
  			$unit = isset($data->sheets[1]['cells'][$i][4])? trim($data->sheets[1]['cells'][$i][4]): '';
  			
  			$closing = isset($data->sheets[1]['cells'][$i][6])? trim($data->sheets[1]['cells'][$i][6]): 0;
  			  			
  			$c = new Criteria();
  			$c->add(ComponentPeer::PRODUCTCODE, $sevcode);
  			$comp = ComponentPeer::doSelectOne($c);
  			
  			
  			if ($comp){
  				  				
  				$c = new Criteria();
  				$c->add(ComponentUnitPeer::NAME, $unit);
  				$cUnit = ComponentUnitPeer::doSelectOne($c);
  				if (!$cUnit){
  					$cUnit = new ComponentUnit();
  					$cUnit->setName($unit);
  					$cUnit->save();  					
  				} 
  				
  				$comp->setComponentunitId($cUnit->getId());
  				
  				$comp->save();
  				
  				$c = new Criteria();
	  			$c->add(WarehousePeer::COMPONENT_ID, $comp->getId());
	  			$wh = WarehousePeer::doSelectOne($c);
	  			if ($wh){
	  				$wh->setClosing($closing);
	  				$wh->setOpening($closing);
	  				$wh->setTotalin(0);
	  				$wh->setTotalout(0);
	  				$wh->setQtyrequested(0);
	  				$wh->setQtyordered(0);
	  				$wh->setQtyneeded(0);
	  				$wh->save();
	  			} else{
	  				$wh = new Warehouse();
	  				$wh->setComponentId($comp->getId());
	  				$wh->setClosing($closing);
	  				$wh->setOpening($closing);
	  				$wh->setTotalin(0);
	  				$wh->setTotalout(0);
	  				$wh->setQtyrequested(0);
	  				$wh->setQtyordered(0);
	  				$wh->setQtyneeded(0);
	  				$wh->save();
//	  				echo "error ". $comp->getId()." $sevcode";
//	  				exit();
	  			}
  			} 
  			else{
  				echo $sevcode."<br/>";
  				
  				$dem++;
//  				$comp = new Component();
//  				$comp->setProductcode($sevcode);
//  				$comp->setDescription($description);
//  				$comp->setBrand($brand);
//  				$comp->setMinstockqty($minstock);
//  				$comp->setLocation($location);
//  				
//  				$c = new Criteria();
//  				$c->add(ComponentUnitPeer::NAME, $unit);
//  				$cUnit = ComponentUnitPeer::doSelectOne($c);
//  				if (!$cUnit){
//  					$cUnit = new ComponentUnit();
//  					$cUnit->setName($unit);
//  					$cUnit->save();
//  					
//  				} 
//  				$comp->setComponentunitId($cUnit->getId());
//  				
//  				$comp->save();
//  				
//  				$c = new Criteria();
//	  			$c->add(WarehousePeer::COMPONENT_ID, $comp->getId());
//	  			$wh = WarehousePeer::doSelectOne($c);
//	  			if (!$wh){
//	  				$wh = new Warehouse();	  				
//	  			}
//	  			$wh->setComponentId($comp->getId());
//  				$wh->setClosing($closing);
//  				$wh->setOpening($closing);
//  				$wh->setTotalin(0);
//  				$wh->setTotalout(0);
//  				$wh->setQtyrequested(0);
//  				$wh->setQtyordered(0);
//  				$wh->setQtyneeded(0);
//  				$wh->save();
	  			
  			}
  		}
  	}
  	echo "success, $dem new";
  	exit();
  		
  }
  
public function executeFixInventoryCategory(){
  	  	
  	
	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\CODE - inventory 20-3.xls');
	
	$dem = 0;
	$dem1=0;
	$cate =0; $type ='';
  	for ($i =2; $i <= 2965; $i++){
  		if (isset($data->sheets[2]['cells'][$i][3]) ){
  			$sevcode = trim($data->sheets[2]['cells'][$i][3]);
  			
  			if ($sevcode == 'MAIN' ){
  				$cate = 0;
  			};
  			if ($sevcode == 'SUB' ){
  				$cate = 1;
  			};
  			if ($sevcode == 'RAW' ){
  				$cate = 2;
  			};
  			
  			if ($sevcode =='MAIN' || $sevcode =='SUB' || $sevcode =='RAW' ){
  				$type = isset($data->sheets[2]['cells'][$i][2])? trim($data->sheets[2]['cells'][$i][2]): NULL;
  				continue;
  			}
  			
  			$desc = isset($data->sheets[2]['cells'][$i][2])? trim($data->sheets[2]['cells'][$i][2]): NULL;
  			
  			$brand = isset($data->sheets[2]['cells'][$i][4])? trim($data->sheets[2]['cells'][$i][4]): NULL;

  			$c = new Criteria();
  			$c->add(ComponentPeer::PRODUCTCODE, $sevcode);
  			$comp = ComponentPeer::doSelectOne($c);
  			
  			if (!$comp){
  				$comp = new Component();
  				$comp->setProductcode($sevcode);
  			}
  			
  			if ($comp){
  				$comp->setCategory($cate);
  				
  				$c = new Criteria();
	  			$c->add(ComponentTypePeer::NAME, $type);
	  			$cType = ComponentTypePeer::doSelectOne($c);
	  			if (!$cType){
	  				$cType = new ComponentType();
	  				$cType->setName($type);
	  				$cType->save();
	  			}
	  			
	  			$comp->setComponenttypeId($cType->getId());
	  			$comp->setDescription($desc);
	  			$comp->setBrand($brand);
  			
  				$comp->save();
  				$dem1++;
  			}
  			
  		}
  	}
  	echo "success $dem $dem1";
  	exit();
  		
  }
  
public function executeListCode(){
  	  	
  	
	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\Code.xls');
	
	$dem1 = 0;
	$dem2=0;
	
	$arrData1 = array();
	$arrData2 = array();
	
	
  	for ($i =4; $i <= 3061; $i++){
  		if (isset($data->sheets[1]['cells'][$i][3]) ){
  			$sevcode = trim($data->sheets[1]['cells'][$i][3]);
  			if ($sevcode == 'MAIN' || $sevcode == 'SUB' || $sevcode == 'RAW'){
  				continue;
  			}
  			$arrData1[$dem1++] = $sevcode;  		
  		}
  	}
  	
	$data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\Inventory_fix.xls');
	
  	for ($i =4; $i <= 2949; $i++){
  		if (isset($data->sheets[0]['cells'][$i][2]) ){
  			$sevcode = trim($data->sheets[0]['cells'][$i][2]);
  			$arrData2[$dem2++] = $sevcode;
  		}
  	}
  	
  	echo "$dem1 $dem2 "; exit();
  	$dem3 = 0;
  	for ($i = 0 ; $i < $dem2; $i++){
  		$bOk = true;
  		for ($j = 0; $j < $dem1; $j++){
  			if ($arrData1[$j]==$arrData2[$i]){
  				$bOk = false;
  				break;
  			}
  		}
  		if ($bOk){
  			echo $arrData2[$i]."<br/>";
  			$dem3++;
  		}
  	}
  	echo "success $dem3 product code not right";
  	exit();
  		
  }
  
 public function executeChangeWarehouse(){
  	  	
  	
	$data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\Inventory_fix.xls');
	$dem2=0;
  	for ($i =4; $i <= 2949; $i++){
  		if (isset($data->sheets[0]['cells'][$i][2]) ){
  			$sevcode = trim($data->sheets[0]['cells'][$i][2]);
  			$arrData[$dem2++] = $sevcode;
  		}
  	}
  	$dem = 0;
  			
  	$c = new Criteria();
  	$lstcomp= ComponentPeer::doSelect($c);
  	foreach ($lstcomp as $comp){
  		$bOk = true;
  		for ($i = 0; $i < $dem2; $i++){
  			if ($comp->getProductcode() == $arrData[$i]){
  				$bOk = false;
  				break;
  			}
  		}
  		if ($bOk){
		  	$c = new Criteria();
		  	$c->add(WarehousePeer::COMPONENT_ID, $comp->getId());
		  	$wh= WarehousePeer::doSelectOne($c);
		  	if (!$wh){
		  		$wh= new Warehouse();
		  		$wh->setComponentId($comp->getId()); 
		  	}
		  	$dem++;
		  	
		  	$wh->setClosing(0);
  			$wh->setOpening(0);
  			$wh->setTotalin(0);
  			$wh->setTotalout(0);
  			$wh->setQtyrequested(0);
  			$wh->setQtyordered(0);
  			$wh->setQtyneeded(0);
		  	$wh->save();
  		}
  	}  	 	
  	
  	
  	echo "success $dem";
  	exit();
  		
  }
  
public function executeDeleteWarehouse(){
  	  	
  	
	
//  	$dem = 0;
//  			
//  	$c = new Criteria();
//  	$lstcomp= WarehousePeer::doSelect($c);
//  	foreach ($lstcomp as $comp){
//  		
//		  	$c = new Criteria();
//		  	$c->add(ComponentPeer::ID, $comp->getComponentId());
//		  	$wh= ComponentPeer::doSelectOne($c);
//		  	if (!$wh){
//		  		$comp->delete();
//			  	$dem++;
//		  	}	  	
//		  	
//  		
//  	}  	 	
  	
  	$dem = 0;
  			
  	$c = new Criteria();
  	$lstcomp= ComponentPeer::doSelect($c);
  	foreach ($lstcomp as $comp){
  		
		  	$c = new Criteria();
		  	$c->add(WarehousePeer::COMPONENT_ID, $comp->getId());
		  	$wh= WarehousePeer::doSelectOne($c);
		  	if (!$wh){
		  		$comp->delete();
			  	$dem++;
		  	}	  	
		  	
  		
  	}  	 	
  	
//  	$dem = 0;
//  			
//  	$c = new Criteria();
//  	$lstcomp= ComponentPeer::doSelect($c);
//  	foreach ($lstcomp as $comp){
//  		$arr1[$dem] = $comp->getProductcode();
//  		$arr[$dem++] = $comp->getDescription();
//  	}  	 	
//  	for ($i =0; $i < $dem; $i++)
//  		for ($j = $i+1;  $j < $dem; $j++){
//  			if (strtolower($arr[$i]) == strtolower($arr[$j])){
//  				//echo "$arr[$i]<br/>";
//  				//echo "$arr1[$i]<br/>";
//  				echo "$arr1[$j]<br/>";
//  			}
//  		}
//  	
  	
  	echo "success $dem";
  	exit();
  		
  }
  
  


public function executeRemoveWarehouse(){
  	  	
  	
//	$data = new Spreadsheet_Excel_Reader();
//	// Set output Encoding.
//	$data->setOutputEncoding('utf-8');	
//	$data->setUTFEncoder('mb');
//	
//	$data->read('D:\PROJECT\SUNLIGHT\Inventory_fix.xls');
//	$dem2=0;
//  	for ($i =4; $i <= 2949; $i++){
//  		if (isset($data->sheets[0]['cells'][$i][2]) ){
//  			$sevcode = trim($data->sheets[0]['cells'][$i][2]);
//  			$arrData[$dem2++] = $sevcode;
//  		}
//  	}
//  	$dem = 0;
//  	for ($i = 0; $i < $dem2; $i++){
//  		for ($j = $i+1 ; $j < $dem2; $j++)
//  			if ($arrData[$i]==$arrData[$j]){
//  				echo $arrData[$i]."<br/>";
//  				$dem++;  				
//  			}
//  	}
//  	echo "success $dem";
//  	exit();
//  	$dem = 0;
//  			
  	$c = new Criteria();
  	$lstcomp= ComponentPeer::doSelect($c);
  	foreach ($lstcomp as $comp){
  		$bOk = true;
  		for ($i = 0; $i < $dem2; $i++){
  			if ($comp->getProductcode() == $arrData[$i]){
  				$bOk = false;
  				break;
  			}
  		}
  		if ($bOk){
		  	$c = new Criteria();
		  	$c->add(WarehousePeer::COMPONENT_ID, $comp->getId());
		  	$wh= WarehousePeer::doSelectOne($c);
		  	if ($wh){
		  		$wh->delete();
		  	}
		  	$comp->delete();
		  	$dem++;
  		}
  	}  	 	
//  	
  	
  	echo "success $dem";
  	exit();
  		
  }
  
public function executeListWarehouse(){
  	  	
	$dem = 0;
  	
	$c = new Criteria();
	$lst = BOMPeer::doSelect($c);	
	foreach ($lst as $obj){
		$firstTime = true;
		$group = $obj->getComponentgroupId();
		$c = new Criteria();
		$c->add(ComponentsPeer::COMPONENTGROUP_ID, $group);
		$lstcomps = ComponentsPeer::doSelect($c);
		foreach ($lstcomps as $comps){
			$compId = $comps->getComponentId();
			$c = new Criteria();
			$c->add(WarehousePeer::COMPONENT_ID, $compId);
			$wh = WarehousePeer::doSelect($c);
			if (!$wh){
				$c = new Criteria();
				$c->add(ComponentPeer::ID, $compId);
				$comp = ComponentPeer::doSelectOne($c);
				if (!$comp){
					echo "BOMerror $compId"; 
				}else{
					if ($firstTime){
						echo "<br/>BOM<br/>".$obj->getId()."<br/>".$obj->getWono()."<br/>";
						$firstTime = false;
					}
					echo $compId."<br/>";
					echo $comp->getProductcode()."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DES: ".$comp->getDescription()."<br/>";
					$dem++;		

					//$comp->delete();
				}
			}
		}
	}
	
	$c = new Criteria();
	$lst = MRPeer::doSelect($c);	
	foreach ($lst as $obj){
		$firstTime = true;
		$group = $obj->getComponentgroupId();
		$c = new Criteria();
		$c->add(ComponentsPeer::COMPONENTGROUP_ID, $group);
		$lstcomps = ComponentsPeer::doSelect($c);
		foreach ($lstcomps as $comps){
			$compId = $comps->getComponentId();
			$c = new Criteria();
			$c->add(WarehousePeer::COMPONENT_ID, $compId);
			$wh = WarehousePeer::doSelect($c);
			if (!$wh){
				$c = new Criteria();
				$c->add(ComponentPeer::ID, $compId);
				$comp = ComponentPeer::doSelectOne($c);
				if (!$comp){
					echo "MRBOMerror $compId"; 
				}else{
					if ($firstTime){
						echo "<br/>MRBOM<br/>".$obj->getId()."<br/>".$obj->getMrno()."<br/>";
						$firstTime = false;
					}
					echo $compId."<br/>";
					echo $comp->getProductcode()."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DES: ".$comp->getDescription()."<br/>";
					$dem++;				

					//$comp->delete();
								
				}
			}
		}
	}
	
	$c = new Criteria();
	$lst = MR2Peer::doSelect($c);	
	foreach ($lst as $obj){
		$firstTime = true;
		$group = $obj->getComponentgroupId();
		$c = new Criteria();
		$c->add(ComponentsPeer::COMPONENTGROUP_ID, $group);
		$lstcomps = ComponentsPeer::doSelect($c);
		foreach ($lstcomps as $comps){
			$compId = $comps->getComponentId();
			$c = new Criteria();
			$c->add(WarehousePeer::COMPONENT_ID, $compId);
			$wh = WarehousePeer::doSelect($c);
			if (!$wh){
				$c = new Criteria();
				$c->add(ComponentPeer::ID, $compId);
				$comp = ComponentPeer::doSelectOne($c);
				if (!$comp){
					echo "MR2error $compId"; 
				}else{
					if ($firstTime){
						echo "<br/>MRSUB<br/>".$obj->getId()."<br/>".$obj->getMrno()."<br/>";
						$firstTime = false;
					}
					echo $compId."<br/>";
					echo $comp->getProductcode()."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DES: ".$comp->getDescription()."<br/>";
					$dem++;							

					//$comp->delete();
					
				}
			}
		}
	}
	
$c = new Criteria();
	$lst = MR3Peer::doSelect($c);	
	foreach ($lst as $obj){
		$firstTime = true;
		$group = $obj->getComponentgroupId();
		$c = new Criteria();
		$c->add(ComponentsPeer::COMPONENTGROUP_ID, $group);
		$lstcomps = ComponentsPeer::doSelect($c);
		foreach ($lstcomps as $comps){
			$compId = $comps->getComponentId();
			$c = new Criteria();
			$c->add(WarehousePeer::COMPONENT_ID, $compId);
			$wh = WarehousePeer::doSelect($c);
			if (!$wh){
				$c = new Criteria();
				$c->add(ComponentPeer::ID, $compId);
				$comp = ComponentPeer::doSelectOne($c);
				if (!$comp){
					echo "MRRawerror $compId"; 
				}else{
					if ($firstTime){
						echo "<br/>MRRAW<br/>".$obj->getId()."<br/>".$obj->getMrno()."<br/>";
						$firstTime = false;
					}
					echo $compId."<br/>";
					echo $comp->getProductcode()."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DES: ".$comp->getDescription()."<br/>";
					$dem++;						

					//$comp->delete();
						
				}
			}
		}
	}
	
	$c = new Criteria();
	$lst = POPeer::doSelect($c);	
	foreach ($lst as $obj){
		$firstTime = true;
		$group = $obj->getComponentgroupId();
		$c = new Criteria();
		$c->add(ComponentsPeer::COMPONENTGROUP_ID, $group);
		$lstcomps = ComponentsPeer::doSelect($c);
		foreach ($lstcomps as $comps){
			$compId = $comps->getComponentId();
			$c = new Criteria();
			$c->add(WarehousePeer::COMPONENT_ID, $compId);
			$wh = WarehousePeer::doSelect($c);
			if (!$wh){
				$c = new Criteria();
				$c->add(ComponentPeer::ID, $compId);
				$comp = ComponentPeer::doSelectOne($c);
				if (!$comp){
					echo "POerror $compId"; 
				}else{
					if ($firstTime){
						echo "<br/>PO<br/>".$obj->getId()."<br/>".$obj->getPono()."<br/>";
						$firstTime = false;
					}
					echo $compId."<br/>";
					echo $comp->getProductcode()."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DES: ".$comp->getDescription()."<br/>";
					$dem++;						

					//$comp->delete();
						
				}
			}
		}
	}
	
	
$c = new Criteria();
	$lst = TallyInPeer::doSelect($c);	
	foreach ($lst as $obj){
		$firstTime = true;
		$group = $obj->getComponentgroupId();
		$c = new Criteria();
		$c->add(ComponentsPeer::COMPONENTGROUP_ID, $group);
		$lstcomps = ComponentsPeer::doSelect($c);
		foreach ($lstcomps as $comps){
			$compId = $comps->getComponentId();
			$c = new Criteria();
			$c->add(WarehousePeer::COMPONENT_ID, $compId);
			$wh = WarehousePeer::doSelect($c);
			if (!$wh){
				$c = new Criteria();
				$c->add(ComponentPeer::ID, $compId);
				$comp = ComponentPeer::doSelectOne($c);
				if (!$comp){
					echo "TIerror $compId"; 
				}else{
					if ($firstTime){
						echo "<br/>TallyIn<br/>".$obj->getId()."<br/>".$obj->getTallyincode()."<br/>";
						$firstTime = false;
					}
					echo $compId."<br/>";
					echo $comp->getProductcode()."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DES: ".$comp->getDescription()."<br/>";
					$dem++;						

					//$comp->delete();
						
				}
			}
		}
	}
	
	
	
$c = new Criteria();
	$lst = TallyOutPeer::doSelect($c);	
	foreach ($lst as $obj){
		$firstTime = true;
		$group = $obj->getComponentgroupId();
		$c = new Criteria();
		$c->add(ComponentsPeer::COMPONENTGROUP_ID, $group);
		$lstcomps = ComponentsPeer::doSelect($c);
		foreach ($lstcomps as $comps){
			$compId = $comps->getComponentId();
			$c = new Criteria();
			$c->add(WarehousePeer::COMPONENT_ID, $compId);
			$wh = WarehousePeer::doSelect($c);
			if (!$wh){
				$c = new Criteria();
				$c->add(ComponentPeer::ID, $compId);
				$comp = ComponentPeer::doSelectOne($c);
				if (!$comp){
					echo "TOerror $compId";
				}else{
					if ($firstTime){
						echo "<br/>TallyOut<br/>".$obj->getId()."<br/>".$obj->getTallyoutcode()."<br/>";
						$firstTime = false;
					}
					echo $compId."<br/>";
					echo $comp->getProductcode()."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DES: ".$comp->getDescription()."<br/>";
					$dem++;				

					//$comp->delete();
								
				}
			}
		}
	}
	
	
  	
  	echo "success $dem";
  	exit();
  		
  }
public function executeListDupCode(){
  	  	
  	
	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\CODE - inventory 20-3.xls');
	
	$dem1 = 0;
	$dem2=0;
	
	$arrData1 = array();
	$arrData2 = array();
	
	
  	for ($i =3; $i <= 2965; $i++){
  		if (isset($data->sheets[2]['cells'][$i][3]) ){
  			$sevcode = trim($data->sheets[2]['cells'][$i][3]);
  			if ($sevcode == 'MAIN' || $sevcode == 'SUB' || $sevcode == 'RAW'){
  				continue;
  			}
  			$arrData1[$dem1] = $sevcode;  		
  			$arrData2[$dem1] = trim($data->sheets[2]['cells'][$i][2]);
  			$dem1++;
  		}
  	}
  	
//  	if (strtolower("ABC") == "abc") echo "1 success";
//  	exit();
  	$dem3 = 0;
	for ($i = 0; $i < $dem1; $i++){
		$bOk = 0;
		for ($j = $i+1; $j< $dem1; $j++){
			if (strtolower($arrData1[$i])==strtolower($arrData1[$j])){
				$bOk = 1;
				$dem2++;
				break;
			}
			if (strtolower($arrData2[$i])==strtolower($arrData2[$j])) {
				$bOk = 2;
				$dem3++;
				break;
			}
			
		}
		if ($bOk ){
			if ($bOk ==1)
				echo $arrData1[$i]."   ".$arrData2[$i]."<br/>";
			else{
				echo $arrData1[$i]."<br/>";
				echo $arrData2[$i]."<br/>";
			}
			
		}
	}
  	echo "success $dem2 $dem3";
  	exit();
  		
  }
  
	public function executeImportNew(){
  	  	
//  	$x = intval('abc');
//  	echo $x;
//  	exit;
  	
	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$data->read('D:\PROJECT\SUNLIGHT\IncorrectCode.xls');
	
	$dem = 0;
	$dem1 = 0;
	$dem2=0;
	$i =1;
	while ($i < 1339){
		if (isset($data->sheets[0]['cells'][$i][1]) ){
			$str = trim($data->sheets[0]['cells'][$i][1]);
			if ($str == 'BOM' || $str == 'MRBOM' ||$str == 'TallyIn' ||$str == 'TallyOut' ||$str == 'MRRAW' ||$str == 'MRSUB' || $str == 'PO' ){
				$i = $i + 3;
				while ($i < 1339){
					if (isset($data->sheets[0]['cells'][$i][1]) ){
						$str = trim($data->sheets[0]['cells'][$i][1]);
						if ($str == 'BOM' || $str == 'MRBOM' ||$str == 'TallyIn' ||$str == 'TallyOut' ||$str == 'MRRAW' ||$str == 'MRSUB' || $str == 'PO' ){
							$i = $i - 1;
							break;
						}
						$number = intval($str);
						if ($str && $str=="$number"){
							$i++;							
							if (isset($data->sheets[0]['cells'][$i][1]) ){
								$str4 = trim($data->sheets[0]['cells'][$i][1]);	$pos = strpos($str4, "DES:");
								$strcode = substr($str4, 0, $pos-8);	$strDesc = trim(substr($str4, $pos + 4));
							}
							if (isset($data->sheets[0]['cells'][$i][2]) ){
								$str2 = trim($data->sheets[0]['cells'][$i][2]);
								if ($str2){
									$c = new Criteria();
									$c->add(ComponentPeer::PRODUCTCODE, $str2);
									$comp = ComponentPeer::doSelectOne($c);
									if (!$comp){
										$dem++;
										$sx = $strDesc;
//										$comp = new Component();
//										$comp->setProductcode($str2);
//										$comp->setDescription($strDesc);
									}			
									else $dem2++;						
									if (isset($data->sheets[0]['cells'][$i+1][1]) ){
										$str3 = trim($data->sheets[0]['cells'][$i+1][1]);
										$number1 = intval($str3);
										if ($str3 && $str3!="$number1"){
											$i= $i+1;
											if (!$comp){
												$sx = $str3;
											}
											//echo "$str3<br/>";											
											//$comp->setDescription($str3);
										}
									}
									if (!$comp){
										//echo "$sx<br/>"; 
										$comp = new Component();
										$comp->setProductcode($str2);
										$comp->setDescription($sx);
										$comp->save();
										
										$c = new Criteria();
										$c->add(ComponentsPeer::COMPONENT_ID, $number);
										$compslst = ComponentsPeer::doSelect($c);
										foreach ($compslst as $comps){
											$comps->setComponentId($comp->getId());
											$comps->save();
										}
										
									}
								}
							} else { 
								//echo "$strDesc<br/>";
								$dem1++;
								$c = new Criteria();
								$c->add(ComponentPeer::PRODUCTCODE, $strcode);
								$comp = ComponentPeer::doSelectOne($c);
								if ($comp){
									echo "error";
									exit();
								}
								$comp = new Component();
								$comp->setProductcode($strcode);
								$comp->setDescription($strDesc);
								$comp->save();
								
								$c = new Criteria();
								$c->add(ComponentsPeer::COMPONENT_ID, $number);
								$compslst = ComponentsPeer::doSelect($c);
								foreach ($compslst as $comps){
									$comps->setComponentId($comp->getId());
									$comps->save();
								}
							}
						}
					}
					$i++;
				}
			}
		}
		$i++;
	}
	
  	echo "success $dem $dem1 $dem2";
  	exit();
  		
  }
  
public function executeFixCode(){
 
  	
	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	$dem = 0;
	$data->read('D:\PROJECT\SUNLIGHT\listcodes21.xls');
	for ($i = 2; $i <= 160; $i++){
		$str = trim($data->sheets[0]['cells'][$i][1]);
		$c = new Criteria();
		$c->add(ComponentPeer::PRODUCTCODE, $str);
		$comp = ComponentPeer::doSelectOne($c);
		
		if (!$comp){
			echo "error $i";
			exit();
		}
		$strdesc = trim($data->sheets[0]['cells'][$i][2]);
		$codenew = isset($data->sheets[0]['cells'][$i][3])? trim($data->sheets[0]['cells'][$i][3]): "";
		$cate = $data->sheets[0]['cells'][$i][4]? $data->sheets[0]['cells'][$i][4]: 0;
		$brand = $data->sheets[0]['cells'][$i][5]? trim($data->sheets[0]['cells'][$i][5]): "";
		$unit = $data->sheets[0]['cells'][$i][6]? trim($data->sheets[0]['cells'][$i][6]): "";
		$type = $data->sheets[0]['cells'][$i][7]? trim($data->sheets[0]['cells'][$i][7]): "";
		
		$c = new Criteria();
		$c->add(WarehousePeer::COMPONENT_ID, $comp->getId());
		$wh = WarehousePeer::doSelectOne($c);
		if (!$wh){
			$dem++;
			$wh = new Warehouse();
			$wh->setComponentId($comp->getId());
			$wh->save();
		}
		if ($codenew){
			$comp->setProductcode($codenew);
		}
		
		$comp->setDescription($strdesc);
		$comp->setCategory($cate);
		if ($brand) $comp->setBrand($brand);
		
		if ($type){
			$c = new Criteria();
	  		$c->add(ComponentTypePeer::NAME, $type);
	  		$cType = ComponentTypePeer::doSelectOne($c);
	  		if (!$cType){
	  			$cType = new ComponentType();
	  			$cType->setName($type);
	  			$cType->save();
	  		}
	  		
	  		$comp->setComponenttypeId($cType->getId());
		}
		if ($unit){
			$c = new Criteria();
  			$c->add(ComponentUnitPeer::NAME, $unit);
  			$cUnit = ComponentUnitPeer::doSelectOne($c);
  			if (!$cUnit){
  				$cUnit = new ComponentUnit();
  				$cUnit->setName($unit);
  				$cUnit->save();
  				
  			} 
  			$comp->setComponentunitId($cUnit->getId());
		}
		$comp->save();
		
	}	
	
	
  	echo "success";
  	exit();
  		
  }
  
public function executeFixCode2(){
  	  	

  	
	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');	
	$data->setUTFEncoder('mb');
	
	$dem2=0;
	$data->read('D:\PROJECT\SUNLIGHT\codechange.xls');
	for ($i = 1; $i <= 13; $i++){
		$s1 = $data->sheets[0]['cells'][$i][2];
		$s2 = $data->sheets[0]['cells'][$i][3];
		$c = new Criteria();
		$c->add(ComponentsPeer::COMPONENT_ID, $s1);
		$compsLst = ComponentsPeer::doSelect($c);
		if ($compsLst){
			$dem=0;
			foreach ($compsLst as $comps){
				$comps->setComponentId($s2);
				$comps->save();
				$dem++;
			}
			echo "$i: $dem<br/>";
		}
		$comp = ComponentPeer::retrieveByPK($s1);
		if ($comp){
			$comp->delete();
			$dem2++;
		}
		
	}	
	
	
  	echo "success $dem2";
  	exit();
  		
  }

  public function executeZeroClosing(){
      $c = new Criteria();
      $whs = WarehousePeer::doSelect($c);
      foreach ($whs as $wh){
          $wh->setClosing(0);
          $wh->setOpening(0);
          $wh->setTotalin(0);
          $wh->setTotalout(0);
          $wh->setQtyneeded(0);
          $wh->setQtyrequested(0);
          $wh->setQtyordered(0);
          $wh->save();

          $c = new Criteria();
          $c->add(ComponentPeer::ID, $wh->getComponentId());
          $comp = ComponentPeer::doSelectOne($c);
          if (!$comp)
              $wh->delete();
      }
      echo "success"; exit();
  }

  public function executeImportClosingExcel(){


	 $data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('utf-8');
	$data->setUTFEncoder('mb');

         
	$dem2=0;
	$data->read('D:\PROJECT\SUNLIGHT\DATABASE\Inventory-2011-05_01.xls');
        $kte =  $data->sheets[0]['cells'][1][1];
        $kte1 =  $data->sheets[0]['cells'][1][2];

	for ($i = 4; $i <= 4105; $i++){
		$closing = $data->sheets[0]['cells'][$i][9];
                $desc = $data->sheets[0]['cells'][$i][3];
                if ($closing > 0){
                    $c = new Criteria();

                    $desc = str_replace($kte, $kte1,$desc);
                    $c->add(ComponentPeer::DESCRIPTION, $desc, Criteria::LIKE);

                    $comps = ComponentPeer::doSelect($c);
                    if (!$comps){
                        echo "Error row $i, $desc khong co trong he thong";
                        exit();
                    }
                    else if (count($comps)> 1){
                        echo "Error row $i, $desc bi trung description";
                        exit();
                    }else{
                        $c1 = new Criteria();
                        $c1->add(WarehousePeer::COMPONENT_ID, $comps[0]->getId());
                        $wh = WarehousePeer::doSelectOne($c1);
                        if (!$wh){
                            echo "Error row $i, $desc khong co warehouse";
                            exit();
                        }else {
                            $wh->setClosing($closing);
                            $wh->setOpening($closing);
                            $wh->save();
                        }
                    }

                }
	}


  	echo "success $dem2";
  	exit();

  }

}